Understanding Consumer Behavior Through Geospatial and Time Data¶
In [1]:
import pandas as pd
import numpy as np
import datetime as dt
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')
import plotly.express as px
%matplotlib inline
import plotly.offline as py
py.offline.init_notebook_mode(connected=True)
DATA CLEANING/WRANGLING¶
In [2]:
data = pd.read_csv("/home/kla/Documents/tests/data_coordinates.csv", delimiter=",", header=0, error_bad_lines=False, low_memory=True)
In [3]:
data.shape
Out[3]:
In [4]:
data.head(5)
Out[4]:
In [5]:
#separating the coordinates column into latitude and longitude
coords = data["coordinates"].str[1:-1].str.split(',', expand=True).astype(float)
coords.columns = ["lat", "long"]
data = pd.concat([data, coords], axis=1)
In [6]:
#transforming dates to datetime objects, parsing them, adding day of week column
data["visit_start"] =pd.to_datetime(data["visit_start"])
data['visit_start_date'] = pd.to_datetime(data['visit_start']).dt.date
data['visit_start_hour'] = pd.to_datetime(data['visit_start']).dt.time
data['visit_end_date'] = pd.to_datetime(data['visit_end']).dt.date
data['visit_end_hour']=pd.to_datetime(data['visit_end']).dt.time
data['visit_day'] = pd.to_datetime(data['visit_start']).dt.day_name()
In [7]:
#ordering the days of the week
days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
data['visit_day'] = pd.Categorical(data['visit_day'], categories=days, ordered=True)
In [8]:
data.head(3)
Out[8]:
In [9]:
#Due to the large file and the slowness of my laptop, I'm sampling only a quarter of the dataset
data = data.sample(frac=.25)
DATA EXPLORATION AND ANALYSIS¶
In [10]:
data.describe()
Out[10]:
In [11]:
data.shape
Out[11]:
In [12]:
sector_sequence = {"sector_name": ["Baumarkt", "Discounter", "Möbelhäuser", "Mode", "Fast Food"]}
color_sequence = ["red", "green", "blue", "goldenrod", "pink"]
Geographical Distribution¶
Here we want to see the distribution of the activities per state and per sector. As we can see Bayern/ Southern Germany has the biggest cluster of activities. Zooming in on the maps, Discounters and Furniture places have the highest density¶
In [13]:
def germany_count():
sample_data = data.sample(n=50000, random_state=1)
fig = px.scatter_mapbox(sample_data, lat="lat", lon="long", hover_name="store_city", color="sector_name", hover_data=["sector_name", "visit_duration_minutes"],
color_discrete_sequence=color_sequence, category_orders= sector_sequence, zoom=5, height=500)
fig.update_layout(mapbox_style="open-street-map")
fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
fig.show()
py.offline.init_notebook_mode(connected=True)
In [14]:
germany_count()
py.offline.init_notebook_mode(connected=True)
In Berlin, Discounters and Furniture Stores have a lot of activities. This could be due to the fact that it is a major urban center and people are always moving in. It is not a big place for Fast Food; in fact, the major point for it is concentrated only in Alexanderplatz area.¶
In [15]:
def berlin_count():
is_berlin = data["store_federal_state"] =="Berlin"
berlin = data[is_berlin]
fig = px.scatter_mapbox(berlin, lat="lat", lon="long",color="sector_name", size="visit_duration_minutes", color_discrete_sequence=color_sequence,
category_orders=sector_sequence, size_max=8, zoom=8)
fig.update_layout(mapbox_style="open-street-map")
fig.update_layout(margin={"r":1,"t":1,"l":2,"b":2})
fig.show()
py.offline.init_notebook_mode(connected=True)
In [16]:
berlin_count()
py.offline.init_notebook_mode(connected=True)
Hamburg is like Berlin in that it is dominated by Discounters and Furniture places. Unlike Berlin, it is less dense and has less users as evidenced by the smaller size of the dots.¶
In [17]:
def hamburg_count():
is_hamburg= data["store_federal_state"] =="Hamburg"
hamburg = data[is_hamburg]
fig = px.scatter_mapbox(hamburg, lat="lat", lon="long",color="sector_name", size="visit_duration_minutes", color_discrete_sequence=color_sequence,
category_orders=sector_sequence, size_max=8, zoom=8)
fig.update_layout(mapbox_style="open-street-map")
fig.update_layout(margin={"r":1,"t":1,"l":2,"b":2})
fig.show()
py.offline.init_notebook_mode(connected=True)
In [18]:
hamburg_count()
py.offline.init_notebook_mode(connected=True)
Bayern is denser than Berlin and Hamurg, with more variety in the sectors. This could be due to the fact that area covered is bigger and has more urban centers (and therefore, more populous and have higher incomes). A lot of activities are centered on places like Munich, Nuremberg, and Frankfurt. These places also have more activities for Furniture Stores, which mirror Berlin and Hamburg. Outside of these areas, Bayern has more activities in Fast Food sector, but my feeling is that this could be the big malls/ shopping centers that one see outside city centers. The duration of visits outside the city centers are also much lower and this is more evident in Bayern than in Berlin and Hamburg.¶
In [44]:
def bayern_count():
is_bayern= data["store_federal_state"] =="Bayern"
bayern = data[is_bayern]
fig = px.scatter_mapbox(bayern, lat="lat", lon="long",color="sector_name", size="visit_duration_minutes", color_discrete_sequence=color_sequence,
category_orders=sector_sequence, size_max=8, zoom=5)
fig.update_layout(mapbox_style="open-street-map")
fig.update_layout(margin={"r":1,"t":1,"l":2,"b":2})
fig.show()
py.offline.init_notebook_mode(connected=True)
In [45]:
bayern_count()
py.offline.init_notebook_mode(connected=True)
USER DATA OVER TIME¶
There is clearly a pattern in the behavior of users over time. There are regular peaks and dips in the data, which indicates a behavioral pattern. In general, people go shopping later in the week, peaking on Fridays. Looking at the trend, number of users is stable until the middle of March. This is the start of the lockdown in Germany, when schools and offices started to close. Despite this, the pattern of doing the shopping at the later part of the week stays.¶
In [21]:
def visit_over_time():
data_user = data.groupby(["visit_start_date"])['user_id'].count()
plt.figure(figsize=(20,8))
plt.ylabel("volume of visits")
plt.xlabel("date of visits")
plt.annotate('start of lockdown in Germany',
(data_user.index[59], data_user[59]),
xytext=(20, 20),
textcoords='offset points')
plt.plot(data_user)
plt.show()
py.offline.init_notebook_mode(connected=True)
In [22]:
visit_over_time()
Where do users go? Here is a breakdown of the shopping trips that users did during the time period. They spent a lot of time buying food. After the lockdown, the amount of visits to Furniture and clothing stores declined. The number of activities in the Baumarkt sector was fairly stable and this could be due to the fact that they are not part of the regular shopping trips for a lot of people.¶
In [23]:
def visit_breakdown():
data_user = data.groupby(["visit_start_date"])['user_id'].count()
fig, ax = plt.subplots(figsize=(20,10))
plt.ylabel("volume of visit")
data.groupby(['visit_start_date', 'sector_name'])['sector_name'].count().unstack().plot(ax=ax, stacked=True, kind="area")
plt.annotate('start of lockdown in Germany',
(data_user.index[59], data_user[59]),
xytext=(20, 20),
textcoords='offset points')
py.offline.init_notebook_mode(connected=True)
In [24]:
visit_breakdown()
Volume of users in stores by day. Food shops and Fast Food have the highest volume across the week.¶
In [25]:
stores = data.groupby(["sector_name", "visit_day"])['user_id'].count().unstack()
stores
Out[25]:
Bayern has the largest number of activities, followed by Berlin. For most part, they mirror the peaks and valleys of each other.¶
In [26]:
def city_breakdown():
data_user = data.groupby(["visit_start_date"])['user_id'].count()
fig, ax = plt.subplots(figsize=(20,10))
plt.ylabel("number of visits")
plt.annotate('start of lockdown in Germany',
(data_user.index[59], data_user[59]),
xytext=(20, 20),
textcoords='offset points')
data.groupby(['visit_start_date', 'store_federal_state'])['store_federal_state'].count().unstack().plot(ax=ax, stacked=True, kind="area")
py.offline.init_notebook_mode(connected=True)
In [27]:
city_breakdown()
When do shoppers go? The peak days are Thurs, Friday, and Saturday for all states. Friday is the busiest, while Sunday is the quietest due to the Sunday ruhetag of the retail shops¶
In [28]:
fig, ax = plt.subplots(figsize=(20,8))
plt.ylabel("number of visits")
data.groupby(['visit_day', 'store_federal_state'])['store_federal_state'].count().unstack().plot(ax=ax, stacked=False, kind="bar")
Out[28]:
How long do shoppers stay? Furniture shopping takes longer than other activities. This is followed by clothes shopping. Shopping for food has the lowest average duration at 32 mins. Meanwhile, Berliners tend to shop quicker than people from Bayern or Hamburg.¶
In [29]:
data.groupby(["sector_name" ])['visit_duration_minutes'].aggregate(np.mean).reset_index()
Out[29]:
In [30]:
data.groupby(["store_federal_state" ])['visit_duration_minutes'].aggregate(np.mean).reset_index()
Out[30]:
In [31]:
store_day = data.groupby(["sector_name", "visit_day", "store_federal_state" ])['visit_duration_minutes'].aggregate(np.mean).reset_index()
Below are the charts that support the findings above. On average, people stay longer in Furniture and clothing stores, with peak activity happening on Friday and Saturday.¶
In [32]:
fig, ax = plt.subplots(figsize=(20,8))
ax= sns.swarmplot(x="visit_day", y="visit_duration_minutes", hue="sector_name", data=store_day, size=10)
ax.set_title("average duration of visit across days by sector")
Out[32]:
In [33]:
fig, ax = plt.subplots(figsize=(20,5))
ax= sns.swarmplot(x="visit_day", y="visit_duration_minutes", hue="store_federal_state", data=store_day, size=10)
ax.set_title("average duration of visits by day broken down by state")
Out[33]:
Looking at population data and store¶
Since the data features location, I thought it would be interesting to see if there's relationship between population density and store information such as sector and length of visit. After all, we assume that places like Aldi or Lidl will do brisk business in residential areas. I found a dataset that has the population of each Postleitzahl in Germany and merged it with the current dataset.¶
In [38]:
population = pd.read_csv("/home/kla/Documents/plz_einwohner.csv", delimiter=",", header=0, error_bad_lines=False, low_memory=True)
In [39]:
population.columns = ["store_zip", "population"]
In [40]:
data_pop = data.merge(population, on="store_zip", how="left")
In [41]:
data_pop.head(3)
Out[41]:
Here we can see that the correlation between population and other factors are very low. This is I think worth investigating. It could be that there is a need to refine location features. Using the zip codes might make a difference but this needs further data retrieval from external sources for the coordinates.¶
In [42]:
corr = data_pop.corr()
plt.figure(figsize = (10, 8))
sns.heatmap(corr,
xticklabels=corr.columns.values,
yticklabels=corr.columns.values,
linewidths=2,
annot=True,
cmap='YlGnBu')
Out[42]:
In [ ]: